package com.jy.dao;


import com.jy.bean.User;
import com.jy.util.DBUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserDao {
    private static final Logger log = LoggerFactory.getLogger(UserDao.class);
    static String now = new SimpleDateFormat("yyyy-MM-dd").format(new Date());

    /**
     * 向User添加一条数据
     * @param user
     * @return 用户注册
     */
    public boolean register(User user){
        boolean flag = false;
        String sql = "insert into m_user (user_name,Email,user_password,receiverAddress,consigneeName,phone,recordDate) values(?,?,?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        conn = DBUtil.getConnection();
        try{
            ps = conn.prepareStatement(sql);
            ps.setString(1,user.getName());
            ps.setString(2, user.getEmail());
            ps.setString(3, user.getPassword());
            ps.setString(4,"无");
            ps.setString(5, "无");
            ps.setString(6, "无");
            ps.setString(7,now);
            int co = ps.executeUpdate();
            if (co>0){
                flag=true;
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn, ps, null);
        }
        return flag;
    }

    /**
     * 判断当前注册的邮箱和账号，是否存在
     * @param Emailbox
     * @param name
     * @return
     */
    public boolean seleuser(String Emailbox,String name){
        boolean fa = false;
        String sql = "select Email,user_name from m_user";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        conn = DBUtil.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                String yx = rs.getString(1);
                String zh = rs.getString(2);
                if (Emailbox.equals(yx)){
                    fa = true;
                }
                if (name.equals(zh)){
                    fa = true;
                }
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return fa;
    }

    /**
     * 查询用户表是否用当前账号
     * @param name
     * @param password
     * @return
     */
    public boolean selelogin(String name,String password){
        boolean fa = false;
        String sql = "select * from m_user where user_name=? and user_password=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        conn = DBUtil.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,password);
            rs = ps.executeQuery();
            while (rs.next()){
                fa = true;
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return fa;
    }

    /**
     * 查询m_controller库中是否有当前输入的账号
     * @param admin
     * @param password
     * @return
     */
    public boolean seleadmin(String admin,String password){
        boolean fva = false;
        String sql = "select * from m_controller where controller_admin=? and controller_password=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        conn = DBUtil.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1,admin);
            ps.setString(2,password);
            rs = ps.executeQuery();
            while (rs.next()){
                fva = true;
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return fva;
    }

    /**
     * 查询所有的用户信息
     * @return
     */
    public List<User> selectuser(){
        List<User> users = new ArrayList<>();
        String sql = "select * from m_user";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt(1));
                user.setName(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setEmail(rs.getString(4));
                user.setAddress(rs.getString(5));
                user.setConsigneeName(rs.getString(6));
                user.setPhone(rs.getString(7));
                user.setDatetime(rs.getString(8));
                log.debug("user:"+String.valueOf(user));
                users.add(user);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return users;
    }

    /**
     * 查询当前用户账号的id
     * @param name
     * @return
     */
    public List<User> selectByname(String name){
        List<User> users = new ArrayList<>();
        String sql = "select * from m_user where user_name=?";
        log.debug("sql:"+sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            rs = ps.executeQuery();
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt(1));
                user.setName(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setEmail(rs.getString(4));
                user.setAddress(rs.getString(5));
                user.setConsigneeName(rs.getString(6));
                user.setDatetime(rs.getString(7));
                users.add(user);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return users;
    }

    /**
     * 根据用户id修改用户的收货信息
     * @param user
     * @return
     */
    public Boolean UpdateUser(User user){
        Boolean favg = false;
        String sql = "UPDATE m_user SET consigneeName=?,phone=?,receiverAddress=? WHERE user_id=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,user.getConsigneeName());
            ps.setString(2,user.getPhone());
            ps.setString(3,user.getAddress());
            ps.setInt(4,user.getId());
            favg = ps.executeUpdate()>0;
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return favg;
    }

    /**
     * 面对对象的方法把sql语句全部集合起来
     * @param params
     * @return
     */
    public List<User> selectByParams(User params){
        List<User> users = new ArrayList<>();
        String sql = createSql(params);
        log.debug("sql:"+sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt(1));
                user.setName(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setEmail(rs.getString(4));
                user.setAddress(rs.getString(5));
                user.setConsigneeName(rs.getString(6));
                user.setPhone(rs.getString(7));
                user.setDatetime(rs.getString(8));
                log.debug(String.valueOf(user));
                users.add(user);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return users;
    }

    private static String createSql(User params) {
        String sql = "select * from m_user where 1=1";
        if (params.getId() != null){
            sql += (" and user_id like '%"+ params.getId() +"%'");
        }
        if (params.getName() != null){
            sql += (" and user_name like '%"+ params.getName() +"%'");
        }
        if (params.getEmail() != null){
            sql += (" and Email like '%" + params.getEmail() +"%'");
        }
        if (params.getDatetime() != null){
            sql += (" and recordDate like '%" + params.getDatetime() +"%'");
        }
        return sql;
    }

    /**
     * 根据传来的用户名来删除该用户
     * @param name
     * @return
     */
    public Boolean Delete_user(String name){
        boolean favg = false;
        String sql = "Delete FROM m_user WHERE user_name =?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            favg = ps.executeUpdate()>0;
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return favg;
    }

    /**
     * 根据当前用户名来查询当前用户的所有信息
     * @param name
     * @return
     */
    public List<User> select_user(String name){
        List<User> users = new ArrayList<>();
        String sql = "SELECT * FROM m_user WHERE user_name=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            rs = ps.executeQuery();
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt(1));
                user.setName(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setEmail(rs.getString(4));
                user.setAddress(rs.getString(5));
                user.setConsigneeName(rs.getString(6));
                user.setPhone(rs.getString(7));
                user.setDatetime(rs.getString(8));
                log.debug("user:"+String.valueOf(user));
                users.add(user);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return users;
    }

    /**
     * 根据当前用户名来修改该用户的信息
     * @param user
     * @return
     */
    public boolean update_user(User user){
        boolean favg = false;
        String sql = "UPDATE m_user SET user_name=?,Email=?,receiverAddress=?,consigneeName=?,recordDate=? WHERE user_name=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps  = conn.prepareStatement(sql);
            ps.setString(1,user.getName());
            ps.setString(2,user.getEmail());
            ps.setString(3,user.getAddress());
            ps.setString(4,user.getConsigneeName());
            ps.setString(5,user.getDatetime());
            ps.setString(6,user.getName());
            favg = ps.executeUpdate()>0;
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return favg;
    }

    /**
     * 根据当前邮箱号来删除绑定该邮箱用户名的密码
     * @param user
     * @return
     */
    public boolean update_passwrod(User user){
        boolean favg = false;
        String sql = "UPDATE m_user SET user_password=? WHERE Email=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,user.getPassword());
            ps.setString(2,user.getEmail());
            favg = ps.executeUpdate()>0;
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return favg;
    }

    public static void main(String[] args) {
//        List<User> users = UserDao.selectuser();
//        for (Object user:users) {
//            System.out.println(user);
//        }
//        log.debug(String.valueOf(update_passwrod(new User(null,null,"long123","3518413017@qq.com",null,null,null,null))));
//        log.debug(createSql(new User(31,null,null,null,null,null,null,null)));
//        boolean favg = UserDao.UpdateUser(new User(31,null,null,null,"为全球","哇大王","1231211121",null));
//        log.debug(String.valueOf(favg));
//        selectuser();
//        log.debug(now);
//        log.debug(String.valueOf(selectByParams(new User(4,null,null,null,null,null,null,null))));
    }
}
